// Created by Windward Studios - no copyright is claimed. This code can be used in
// any manner by anyone for any reason. There is no copyright of any kind on it. You may
// use it in commercial products. You may change it without sharing those changes.
// We ask that you keep the "created by Windward Studios" in a comment at the top.

using System;
using System.Collections;
using System.Collections.Generic;
using System.Data;
using System.Data.Common;
using System.Data.OleDb;
using Kailua.net.windward.utils;
using net.windward.utils.ado.ODBC;

namespace net.windward.utils.ado.OleDb
{
	/// <summary>
	/// An OleDb database.
	/// 
	/// </summary>
	public class WrOleDbDatabase : IWrDatabase
	{
		private readonly DbProviderFactory provider;
		private readonly string oledbProvider;
		private readonly string server;
		private readonly string database;

		private WrTable[] tables;
		private WrView[] views;
		private WrStoredProcedure[] procedures;

		private static readonly Hashtable types;
		static WrOleDbDatabase()
		{
			types = new Hashtable();
			types.Add(2, DbType.Int16);
			types.Add(3, DbType.Int32);
			types.Add(4, DbType.Double);
			types.Add(5, DbType.Single);
			types.Add(6, DbType.Decimal);
			types.Add(11, DbType.Boolean);
			types.Add(17, DbType.Byte);
			types.Add(20, DbType.Int64);
			types.Add(72, DbType.Guid);
			types.Add(128, DbType.Binary);
			types.Add(129, DbType.String);
			types.Add(130, DbType.String);
			types.Add(131, DbType.Decimal);
			types.Add(135, DbType.DateTime);
		}

		/// <summary>
		/// Create a SqlServer database object.
		/// </summary>
		/// <param name="provider">The provider for this database.</param>
		/// <param name="oledbProvider">The OleDb Provider used.</param>
		/// <param name="server">The name of the server the database is on.</param>
		/// <param name="database">The name of the database.</param>
		public WrOleDbDatabase(DbProviderFactory provider, string oledbProvider, string server, string database)
		{
			this.provider = provider;
			this.oledbProvider = oledbProvider;
			this.server = server;
			this.database = database;
		}

		/// <summary>
		/// The default schema for all objects in this database. Returns null if there is no default.
		/// </summary>
		public string DefaultSchema
		{
			get
			{
				return null;
			}
		}

		/// <summary>
		/// The name of the database.
		/// </summary>
		public string Name
		{
			get { return database; }
		}

		/// <summary>
		/// All tables in this database. Must call LoadMetadata first.
		/// </summary>
		public WrTable[] Tables
		{
			get { return tables; }
		}

		/// <summary>
		/// All views in this database. Must call LoadMetadata first.
		/// </summary>
		public WrView[] Views
		{
			get { return views; }
		}

		/// <summary>
		/// Return all stored procedures in this database. Must call LoadMetadata first.
		/// </summary>
		public WrStoredProcedure[] StoredProcedures
		{
			get { return procedures; }
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="credentials">The user credentials to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(WrCredentials credentials, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{
			LoadMetadata(GetCommandLine(credentials), owner, status);
		}

		/// <summary>
		/// Loads the metadata for this database.
		/// </summary>
		/// <param name="connStr">The connection string to access the database.</param>
		/// <param name="owner">Determines what metadata objects are read and if full metadata is retrieved.</param>
		/// <param name="status">Pass the status of loading the metadata.</param>
		public void LoadMetadata(string connStr, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.START, database);
			
			tables = _GetTables(connStr, owner, status);
			for (int index = 0; index < tables.Length; index++)
			{
				WrColumn[] columns = _GetColumns(tables[index], connStr);
				tables[index] = new WrTable(tables[index].Schema, tables[index].Name, tables[index].Description, tables[index].IsSystem, columns);
			}

			views = _GetViews(connStr, owner, status);
			for (int index = 0; index < views.Length; index++)
			{
				WrColumn[] columns = _GetColumns(views[index], connStr);
				views[index] = new WrView(views[index].Schema, views[index].Name, views[index].Description, views[index].IsSystem, columns);
			}

			procedures = _GetStoredProcedures(connStr, owner, status);
		}

		private static WrTable[] _GetTables(string connectionString, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.TABLES, null);
			
			ArrayList rtn = new ArrayList();
			using (OleDbConnection conn = new OleDbConnection(connectionString))
			{
				conn.Open();
				if ((owner & WrVendor.ELEM_OWNER.USER) != 0)
				{
					DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "TABLE"});
					int ordDesc = schemaTable.Columns.IndexOf("DESCRIPTION");

					foreach (DataRow rowOn in schemaTable.Rows)
					{
						string tableName = (string) rowOn["TABLE_NAME"];
						if (status != null)
							status(WrDatabase.TYPE.TABLE, tableName);
						rtn.Add(new WrTable(null, tableName, rowOn.IsNull(ordDesc) ? "" : (string)rowOn[ordDesc],
						                    false, null));
					}
				}
				if ((owner & WrVendor.ELEM_OWNER.SYSTEM) != 0)
				{
					Trap.trap();
					DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "SYSTEM TABLE" });
					int ordDesc = schemaTable.Columns.IndexOf("DESCRIPTION");

					foreach (DataRow rowOn in schemaTable.Rows)
					{
						string tableName = (string)rowOn["TABLE_NAME"];
						if (status != null)
							status(WrDatabase.TYPE.TABLE, tableName);
						rtn.Add(new WrTable(null, tableName, rowOn.IsNull(ordDesc) ? "" : (string)rowOn[ordDesc],
						                    false, null));
					}
				}
			}
			rtn.Sort();
			return (WrTable[])rtn.ToArray(typeof(WrTable));
		}

		private static WrView[] _GetViews(string connectionString, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.VIEWS, null);

			ArrayList rtn = new ArrayList();
			using (OleDbConnection conn = new OleDbConnection(connectionString))
			{
				conn.Open();
				if ((owner & WrVendor.ELEM_OWNER.USER) != 0)
				{
					DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] {null, null, null, "VIEW"});
					int ordDesc = schemaTable.Columns.IndexOf("DESCRIPTION");

					foreach (DataRow rowOn in schemaTable.Rows)
					{
						string viewName = (string)rowOn["TABLE_NAME"];
						if (status != null)
							status(WrDatabase.TYPE.VIEW, viewName);
						rtn.Add(new WrView(null, viewName, rowOn.IsNull(ordDesc) ? "" : (string)rowOn[ordDesc], false,
						                   null));
					}
				}
				if ((owner & WrVendor.ELEM_OWNER.SYSTEM) != 0)
				{
					Trap.trap();
					DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, new object[] { null, null, null, "SYSTEM VIEW" });
					int ordDesc = schemaTable.Columns.IndexOf("DESCRIPTION");

					foreach (DataRow rowOn in schemaTable.Rows)
					{
						string viewName = (string)rowOn["TABLE_NAME"];
						if (status != null)
							status(WrDatabase.TYPE.VIEW, viewName);
						rtn.Add(new WrView(null, viewName, rowOn.IsNull(ordDesc) ? "" : (string)rowOn[ordDesc], false,
						                   null));
					}
				}
			}
			rtn.Sort();
			return (WrView[])rtn.ToArray(typeof(WrView));
		}

		private static WrColumn[] _GetColumns(WrBaseTable table, string connectionString)
		{
			ArrayList rtn = new ArrayList();
			using (OleDbConnection conn = new OleDbConnection(connectionString))
			{
				conn.Open();
				DataTable schemaTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Columns, new object[] { null, null, table.FullName, null });
				int ordDesc = schemaTable.Columns.IndexOf("DESCRIPTION");
				foreach (DataRow rowOn in schemaTable.Rows)
				{
					int oleType = (int)rowOn["DATA_TYPE"];
					DbType typ = types.Contains(oleType) ? (DbType)types[oleType] : DbType.Object;
					rtn.Add(new WrColumn((string)rowOn["COLUMN_NAME"], rowOn.IsNull(ordDesc) ? "" : (string)rowOn[ordDesc], typ));
				}
				// bugbug - can use OleDbSchemaGuid.Foreign_Keys & OleDbSchemaGuid.Primary_Keys to get keys
			}
			return (WrColumn[])rtn.ToArray(typeof(WrColumn));
		}

		private static WrStoredProcedure[] _GetStoredProcedures(string connectionString, WrVendor.ELEM_OWNER owner, WrDatabase.LoadStatus status)
		{

			if (status != null)
				status(WrDatabase.TYPE.PROCEDURES, null);

			List<WrStoredProcedure> rtn = new List<WrStoredProcedure>();
			// Excel has trouble with this
			try
			{
				using (OleDbConnection conn = new OleDbConnection(connectionString))
				{
					conn.Open();
					DataTable procTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedures, new object[] {null, null, null});
					int ordDesc = procTable.Columns.IndexOf("DESCRIPTION");
					foreach (DataRow rowOn in procTable.Rows)
					{
						string name = (string) rowOn["PROCEDURE_NAME"];
						if (name.IndexOf(';') != -1)
							name = name.Substring(0, name.IndexOf(';'));
						if (status != null)
							status(WrDatabase.TYPE.PROCEDURE, name);

						bool isSys = false;
						if ((((string) rowOn["PROCEDURE_SCHEMA"]).ToUpper() == "INFORMATION_SCHEMA") ||
						    (((string) rowOn["PROCEDURE_SCHEMA"]).ToUpper() == "SYS"))
							isSys = true;
						if (name.StartsWith("dt_")) // only way I could find to skip system ones
							isSys = true;
						if ((isSys && ((owner & WrVendor.ELEM_OWNER.SYSTEM) == 0)))
							continue;
						if (((!isSys) && ((owner & WrVendor.ELEM_OWNER.USER) == 0)))
							continue;

						// parameters
						DataTable paramTable = conn.GetOleDbSchemaTable(OleDbSchemaGuid.Procedure_Parameters,
						                                                new object[] {null, null, name});
						DataColumn parmNameDataColumn = paramTable.Columns["PARAMETER_NAME"];
						DataColumn parmDirectionColumn = paramTable.Columns["PARAMETER_TYPE"];
						DataColumn parmTypeDataColumn = paramTable.Columns["TYPE_NAME"];
						DataColumn parmSizeColumn = paramTable.Columns["NUMERIC_PRECISION"];
						List<WrProcParam> procParams = new List<WrProcParam>();
						foreach (DataRow parmRow in paramTable.Rows)
						{
							ParameterDirection dir;
							switch (int.Parse(parmRow[parmDirectionColumn].ToString()))
							{
								case 1:
									dir = ParameterDirection.Input;
									break;
								case 2:
									dir = ParameterDirection.Output;
									break;
								case 4:
									dir = ParameterDirection.ReturnValue;
									break;
								default:
									Trap.trap(); // undocumented
									dir = ParameterDirection.InputOutput;
									break;
							}
							int size = parmRow.IsNull(parmSizeColumn) ? 0 : int.Parse(parmRow[parmSizeColumn].ToString());
							procParams.Add(new WrProcParam(parmRow[parmNameDataColumn].ToString(),
							                               WrOdbcDatabase.ConvertFromRawType(parmRow[parmTypeDataColumn].ToString()),
							                               size, false, dir));
						}

						rtn.Add(new WrStoredProcedure(null, name, rowOn.IsNull(ordDesc) ? "" : (string) rowOn[ordDesc], false,
						                              procParams.ToArray()));
					}
				}
				rtn.Sort();
			} catch (Exception)
			{
				// just return an empty list
			}
			return rtn.ToArray();
		}

		private string GetCommandLine(WrCredentials credentials)
		{
			return WrOleDbVendor.ConnectionString(provider, oledbProvider, server, database, credentials, true);
		}
	}
}
